﻿
CREATE PROCEDURE [dbo].[proc_Customer_GetEmployeeList_MySearch]
(
	@UserName nvarchar(50),
	@Customer nvarchar(100)
)
As
Begin
	declare @companyid int,@Days int,@DepartmentId int
	EXEC @companyid=proc_Company_GetID @UserName,@companyid--单位ID
	set @DepartmentId = (Select DepartmentId From Employee Where UserName=@UserName)--部门Id
	Set @Days = (Select ProtectDays From Department Where id=@DepartmentId)--系统保护天数

	Select 
		Id,
		delFlag,
		CustomerId,
		CustomerName,
		Project,
		ProtectId,
		isnull((Select Title From Customer_Source Where Id=Customer.SourceId),'...') As Source,
		IsNull(ProtectDate,'1900-01-01') As ProtectDate,
		IsNull((@Days-datediff(day,Isnull(ProtectDate,GetDate()),GetDate())),0) As Countdown,
		isnull((Select Title From Customer_Industry Where Id=Customer.IndustryOneId),'...') As IndustryOne,
		isnull((Select Title From Customer_Type Where Id=Customer.TypeId),'...') As Type,
		CreateDate
	From Customer
--Where CustomerName like '%'+@Customer+'%' and 
--ProtectName=@UserName and Audit=1 and Companyid=@companyid
--Order by ProtectId ASC ,UpTime ASC
	Where ProtectName=@UserName and Audit=1 and Companyid=@companyid And (@Customer = '' Or CustomerName like '%'+@Customer+'%')
	Order by ProtectDate Desc,ProtectId ASC ,UpTime ASC
End